#使用原生态SQL 插入数据库


import pymysql
from .settings import MYSQL_SETTINGS


class MysqlNatruePipline(object):
    def __init__(self,db):
        self.db = db

    def __del__(self):

        try:
            self.db.close()
        except:
            pass

    @classmethod
    def from_settings(cls, settings):

        host=MYSQL_SETTINGS['HOST']

        database=MYSQL_SETTINGS['DATABASE']

        user=MYSQL_SETTINGS['USER']

        passwd=MYSQL_SETTINGS['PASSWORD']

        charset=MYSQL_SETTINGS['CHARSET']

        #如果数据库不存在，则创建数据库
        try:
            db = pymysql.connect(host, user, passwd, "", charset=charset)
            cursor = db.cursor()
            cursor.execute(f"create database IF NOT EXISTS {database} default charset=utf8;")
            db.commit()
        except:
            pass
        finally:
            db.close()

        # 如果数据库中表不存在，则创建表

        db = pymysql.connect(host, user, passwd, database, charset=charset)
        cursor = db.cursor()

        try:



            sql = """
                CREATE TABLE IF NOT EXISTS jd_goods (  
                        id int(11) PRIMARY  KEY AUTO_INCREMENT COMMENT '设置主键自增',  
                        goods_title varchar(200) NULL COMMENT '商品标题',  
                        goods_price varchar(10) DEFAULT NULL COMMENT '商品价格',  
                        goods_detail_url varchar(200) DEFAULT NULL COMMENT '详细页网址',  
                        goods_image_url varchar(200) DEFAULT NULL COMMENT '图片网址',  
                        goods_image_path varchar(200) DEFAULT NULL COMMENT '图片保存本地路径' 
                        ) ENGINE=InnoDB AUTO_INCREMENT=361 DEFAULT CHARSET=utf8 ;
            """

            cursor.execute(sql)
            db.commit()
        except:
            pass




        return cls(db)

    #process_item
    def process_item(self, item, spider):
        '''

        :param item:
        :param spider:
        :return:
        '''

        cursor = self.db.cursor()

        self.db_insert(cursor,item)



        return item

    def db_insert(self, cursor, item):
        # 执行具体的插入
        # 根据不同的item 构建不同的sql语句并插入到mysql中

        try:

            insert_sql, params = self.get_insert_sql(item)

            cursor.execute(insert_sql, params)

            self.db.commit()
        except:

            self.db.rollback()

    def db_CmdSQL(self, cmdSql):
        # 执行具体的插入
        # 根据不同的item 构建不同的sql语句并插入到mysql中

        try:

            cursor = self.db.cursor()

            cursor.execute(cmdSql)

            self.db.commit()
        except:

            self.db.rollback()




    def get_insert_sql(self, item):
        insert_sql = """
                INSERT INTO jd_goods(goods_title,
                                    goods_price,
                                    goods_image_url,
                                    goods_detail_url,
                                    goods_image_path) 
                                    VALUES (%s, %s, %s, %s, %s)
        """
        params = (
            item["goods_title"],
            item["goods_price"],
            item["goods_image_url"],
            item["goods_detail_url"],
            item["goods_image_path"])
        return insert_sql, params


